package com.ops.uitl.jdbc;

import java.io.StringReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.Types;
import org.apache.log4j.Logger;
import com.ops.uitl.data.IData;
import com.ops.uitl.data.IDataset;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.ArrayList;
import java.util.List;
import java.util.Iterator;

public class StdParameterStatement implements IStatement {
	
	protected final Logger log = Logger.getLogger(this.getClass());
	
	private PreparedStatement statement;
	private String sql;
	private IData param;
	private IDataset params;
	private String[] names;
	
	/**
	 * construct function
	 * @param conn
	 * @param sqlstr
	 * @throws Exception
	 */
	public StdParameterStatement(Connection conn, String sqlstr, IData param) throws Exception {
		preprocStatement(sqlstr);
		statement = conn.prepareStatement(sql);
		this.param = param;
	}
	
	/**
	 * construct function
	 * @param conn
	 * @param sqlstr
	 * @param params
	 * @throws Exception
	 */
	public StdParameterStatement(Connection conn, String sqlstr, IDataset params) throws Exception {
		preprocStatement(sqlstr);
		statement = conn.prepareStatement(sql);
		this.params = params;
	}
		
	/**
	 * get sql
	 * @return String
	 * @throws Exception
	 */
    public String getSql() throws Exception {
    	return sql;
    }

	/**
	 * get statement
	 * @return Statement
	 * @throws Exception
	 */
    public Statement getStatement() throws Exception {
    	return statement;
    }

	/**
	 * execute query
	 * @return ResultSet
	 * @throws Exception
	 */
    public ResultSet executeQuery() throws Exception {
    	if (log.isDebugEnabled()) log.debug("SQL " + sql);
    	if (log.isDebugEnabled()) log.debug("PARAM " + param);
    	
    	setParameters(statement, names, param);
    	
    	try {
    		long beginTime = System.currentTimeMillis();
    		ResultSet result = statement.executeQuery();
    		DaoHelper.getExecuteTimeBySql(log, beginTime);
    		return result;
    	} catch (java.sql.SQLException e) {
    		log.error("ERROR SQL " + sql);
    		throw e;
    	}
    }
    
    /**
     * execute update
     * @return int
     * @throws Exception
     */
    public int executeUpdate() throws Exception {
    	if (log.isDebugEnabled()) log.debug("SQL " + sql);
    	if (log.isDebugEnabled()) log.debug("PARAM " + param);
    	
    	setParameters(statement, names, param);
    	
    	try {
    		long beginTime = System.currentTimeMillis();
    		int result = statement.executeUpdate();
    		DaoHelper.getExecuteTimeBySql(log, beginTime);
    		return result;
    	} catch (java.sql.SQLException e) {
    		log.error("ERROR SQL " + sql);
    		throw e;
    	}
    }
    
    /**
     * execute batch
     * @return int[]
     * @throws Exception
     */
    public int[] executeBatch() throws Exception {
    	if (params.size() == 0) return new int[] {};
    	
    	if (log.isDebugEnabled()) log.debug("Batch SQL " + sql);
    	
    	for (int i=0; i<params.size(); i++) {
    		setParameters(statement, names, (IData) params.get(i));
    		if (log.isDebugEnabled()) log.debug("Batch PARAM " + params.get(i));
    		statement.addBatch();
    	}
    	
    	try {
	    	long beginTime = System.currentTimeMillis();
	    	int[] result = statement.executeBatch();
	    	DaoHelper.getExecuteTimeBySql(log, beginTime);
	    	return result;
    	} catch (java.sql.SQLException e) {
    		log.error("ERROR Batch SQL " + sql);
    		throw e;
    	}
    }

	/**
	 * close statement
	 * @throws Exception
	 */
	public void close() throws Exception {
		statement.close();
	}
	
	/**
	 * set parameters
	 * @param statement
	 * @param param
	 * @throws Exception
	 */
	public void setParameters(PreparedStatement statment, String[] names, IData param) throws Exception {
    	for (int i=0; i<names.length; i++) {
    		Object value = param.get(names[i]);
    		if (value == null) {
    			statement.setNull(i + 1, Types.VARCHAR);
    			if (log.isDebugEnabled()) log.debug("["+(i+1)+"]BINDING ["+names[i]+"] null");
    		} else {
    			if (value instanceof ColumnObject) {
    				ColumnObject columnObject = (ColumnObject) value;
    				if (columnObject.getType() == Types.LONGVARCHAR) {
    					statement.setCharacterStream(i + 1, new StringReader(columnObject.getValue().toString()), columnObject.getValue().toString().length());
    				}
    			} else {
    				statement.setObject(i + 1, value);
    			}
    			if (log.isDebugEnabled()) log.debug("["+(i+1)+"]BINDING ["+names[i]+"] ["+value+"]");
    		}
    	}
	}
	
	/**
	 * preproc statement
	 * @param sqlstr
	 * @return String
	 * @throws Exception
	 */
	private void preprocStatement(String sqlstr) throws Exception {
		class QuoteRange {
			int start;
			int length;
			String text;
		}
		Pattern patParam = Pattern.compile("(:[a-zA-Z_0-9\\$]*)");
		Pattern patQuote = Pattern.compile("('[^']*')");
		Matcher matcher;
		List quoteRanges = new ArrayList();
		matcher = patQuote.matcher(sqlstr);
		while (matcher.find()) {
			QuoteRange r = new QuoteRange();
			r.start = matcher.start();
			r.text = matcher.group();
			r.length = r.text.length();
			quoteRanges.add(r);
		}
		matcher = patParam.matcher(sqlstr);
		List keys = new ArrayList();
		while (matcher.find()) {
			String key = matcher.group().substring(1);
			if (!quoteRanges.isEmpty()) {
				boolean skip = false;
				int pos = matcher.start();
				Iterator it = quoteRanges.iterator();
				while (it.hasNext()) {
					QuoteRange r = (QuoteRange)it.next();
					if (pos >= r.start && pos < r.start+r.length) {
						skip = true;
						break;
					}
				}
				if (skip) {
					continue;
				}
			}
			if (key.length() > 0) {
				sqlstr = sqlstr.replaceFirst(":" + key.replaceAll("\\Q$\\E", "\\\\\\$"), "?");
				keys.add(key);
			}
		}

		this.sql = sqlstr;
		this.names = (String[]) keys.toArray(new String[0]);
	}

}